USE [eShop]
GO

/****** Object:  StoredProcedure [dbo].[USP_EmployeeInfo_Query]    Script Date: 10/16/2011 05:48:48 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USP_EmployeeInfo_Query]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[USP_EmployeeInfo_Query]
GO

/****** Object:  StoredProcedure [dbo].[USP_Add_EmployeeInfo]    Script Date: 10/16/2011 05:48:48 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USP_Add_EmployeeInfo]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[USP_Add_EmployeeInfo]
GO

/****** Object:  StoredProcedure [dbo].[USP_Add_MerchandiseBasicInfo]    Script Date: 10/16/2011 05:48:48 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USP_Add_MerchandiseBasicInfo]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[USP_Add_MerchandiseBasicInfo]
GO

/****** Object:  StoredProcedure [dbo].[USP_EmployeeInfo_Update]    Script Date: 10/16/2011 05:48:48 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USP_EmployeeInfo_Update]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[USP_EmployeeInfo_Update]
GO

/****** Object:  StoredProcedure [dbo].[USP_AppendMerchandiseClass]    Script Date: 10/16/2011 05:48:48 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USP_AppendMerchandiseClass]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[USP_AppendMerchandiseClass]
GO

/****** Object:  StoredProcedure [dbo].[USP_EmployeeLogin]    Script Date: 10/16/2011 05:48:48 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USP_EmployeeLogin]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[USP_EmployeeLogin]
GO

/****** Object:  StoredProcedure [dbo].[USP_GetSigleSellMerchandiseInfo]    Script Date: 10/16/2011 05:48:48 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USP_GetSigleSellMerchandiseInfo]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[USP_GetSigleSellMerchandiseInfo]
GO

/****** Object:  StoredProcedure [dbo].[USP_GetAllEmployeePermission]    Script Date: 10/16/2011 05:48:48 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USP_GetAllEmployeePermission]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[USP_GetAllEmployeePermission]
GO

/****** Object:  StoredProcedure [dbo].[USP_GetStockExamineList]    Script Date: 10/16/2011 05:48:48 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USP_GetStockExamineList]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[USP_GetStockExamineList]
GO

/****** Object:  StoredProcedure [dbo].[USP_GetStockToSellData]    Script Date: 10/16/2011 05:48:48 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USP_GetStockToSellData]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[USP_GetStockToSellData]
GO

/****** Object:  StoredProcedure [dbo].[USP_Merchandise_BasicInfo_Delete]    Script Date: 10/16/2011 05:48:48 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USP_Merchandise_BasicInfo_Delete]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[USP_Merchandise_BasicInfo_Delete]
GO

/****** Object:  StoredProcedure [dbo].[USP_Merchandise_BasicInfo_GetAllList]    Script Date: 10/16/2011 05:48:48 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USP_Merchandise_BasicInfo_GetAllList]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[USP_Merchandise_BasicInfo_GetAllList]
GO

/****** Object:  StoredProcedure [dbo].[USP_Merchandise_BasicInfo_Update]    Script Date: 10/16/2011 05:48:48 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USP_Merchandise_BasicInfo_Update]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[USP_Merchandise_BasicInfo_Update]
GO

/****** Object:  StoredProcedure [dbo].[USP_Merchandise_Order_ADD]    Script Date: 10/16/2011 05:48:48 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USP_Merchandise_Order_ADD]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[USP_Merchandise_Order_ADD]
GO

/****** Object:  StoredProcedure [dbo].[USP_Merchandise_Class_GetList]    Script Date: 10/16/2011 05:48:48 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USP_Merchandise_Class_GetList]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[USP_Merchandise_Class_GetList]
GO

/****** Object:  StoredProcedure [dbo].[USP_Merchandise_Sell_Query]    Script Date: 10/16/2011 05:48:48 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USP_Merchandise_Sell_Query]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[USP_Merchandise_Sell_Query]
GO

/****** Object:  StoredProcedure [dbo].[USP_Merchandise_OrderDetail_ADD]    Script Date: 10/16/2011 05:48:48 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USP_Merchandise_OrderDetail_ADD]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[USP_Merchandise_OrderDetail_ADD]
GO

/****** Object:  StoredProcedure [dbo].[USP_MerchandiseSee_UpdateQuantity]    Script Date: 10/16/2011 05:48:48 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USP_MerchandiseSee_UpdateQuantity]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[USP_MerchandiseSee_UpdateQuantity]
GO

/****** Object:  StoredProcedure [dbo].[USP_Merchandise_Unit_GetList]    Script Date: 10/16/2011 05:48:48 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USP_Merchandise_Unit_GetList]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[USP_Merchandise_Unit_GetList]
GO

/****** Object:  StoredProcedure [dbo].[USP_MerchandiseSell_ADD]    Script Date: 10/16/2011 05:48:48 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USP_MerchandiseSell_ADD]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[USP_MerchandiseSell_ADD]
GO

/****** Object:  StoredProcedure [dbo].[USP_MerchandiseSell_Query]    Script Date: 10/16/2011 05:48:48 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USP_MerchandiseSell_Query]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[USP_MerchandiseSell_Query]
GO

/****** Object:  StoredProcedure [dbo].[USP_MerchandiseSell_ToSell]    Script Date: 10/16/2011 05:48:48 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USP_MerchandiseSell_ToSell]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[USP_MerchandiseSell_ToSell]
GO

/****** Object:  StoredProcedure [dbo].[USP_QueryMerchandiseClass]    Script Date: 10/16/2011 05:48:48 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USP_QueryMerchandiseClass]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[USP_QueryMerchandiseClass]
GO

/****** Object:  StoredProcedure [dbo].[USP_Search_MerchandiseBasicInfo]    Script Date: 10/16/2011 05:48:48 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USP_Search_MerchandiseBasicInfo]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[USP_Search_MerchandiseBasicInfo]
GO

/****** Object:  StoredProcedure [dbo].[USP_StockIn_Append]    Script Date: 10/16/2011 05:48:48 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USP_StockIn_Append]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[USP_StockIn_Append]
GO

/****** Object:  StoredProcedure [dbo].[USP_UpdateEmployeePermission]    Script Date: 10/16/2011 05:48:48 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USP_UpdateEmployeePermission]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[USP_UpdateEmployeePermission]
GO

/****** Object:  StoredProcedure [dbo].[USP_Update_MerchandiseSell]    Script Date: 10/16/2011 05:48:48 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USP_Update_MerchandiseSell]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[USP_Update_MerchandiseSell]
GO

/****** Object:  StoredProcedure [dbo].[USP_UpdateMerchandiseClass]    Script Date: 10/16/2011 05:48:48 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USP_UpdateMerchandiseClass]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[USP_UpdateMerchandiseClass]
GO

/****** Object:  StoredProcedure [dbo].[USP_MerchandiseAlterStatus_Update]    Script Date: 10/16/2011 05:48:48 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USP_MerchandiseAlterStatus_Update]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[USP_MerchandiseAlterStatus_Update]
GO

/****** Object:  StoredProcedure [dbo].[USP_MerchandiseUnit_Append]    Script Date: 10/16/2011 05:48:48 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USP_MerchandiseUnit_Append]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[USP_MerchandiseUnit_Append]
GO

/****** Object:  StoredProcedure [dbo].[USP_MerchandiseTodaySelled_Query]    Script Date: 10/16/2011 05:48:48 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USP_MerchandiseTodaySelled_Query]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[USP_MerchandiseTodaySelled_Query]
GO

/****** Object:  StoredProcedure [dbo].[USP_MerchandiseAlterNumCheck]    Script Date: 10/16/2011 05:48:48 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USP_MerchandiseAlterNumCheck]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[USP_MerchandiseAlterNumCheck]
GO

/****** Object:  StoredProcedure [dbo].[USP_MerchandiseOverTimeCheck]    Script Date: 10/16/2011 05:48:48 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USP_MerchandiseOverTimeCheck]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[USP_MerchandiseOverTimeCheck]
GO

/****** Object:  StoredProcedure [dbo].[USP_SelledEmployee]    Script Date: 10/16/2011 05:48:48 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USP_SelledEmployee]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[USP_SelledEmployee]
GO

/****** Object:  StoredProcedure [dbo].[USP_TrendChart]    Script Date: 10/16/2011 05:48:48 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USP_TrendChart]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[USP_TrendChart]
GO

/****** Object:  StoredProcedure [dbo].[USP_HotMerchandiseQuery]    Script Date: 10/16/2011 05:48:48 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USP_HotMerchandiseQuery]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[USP_HotMerchandiseQuery]
GO

/****** Object:  StoredProcedure [dbo].[USP_MemberQuery]    Script Date: 10/16/2011 05:48:48 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USP_MemberQuery]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[USP_MemberQuery]
GO

/****** Object:  StoredProcedure [dbo].[USP_MemberInfo_Append]    Script Date: 10/16/2011 05:48:48 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USP_MemberInfo_Append]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[USP_MemberInfo_Append]
GO

/****** Object:  StoredProcedure [dbo].[USP_MemberLevelInfo_Query]    Script Date: 10/16/2011 05:48:48 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USP_MemberLevelInfo_Query]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[USP_MemberLevelInfo_Query]
GO

/****** Object:  StoredProcedure [dbo].[USP_MemberScoreAppend]    Script Date: 10/16/2011 05:48:48 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USP_MemberScoreAppend]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[USP_MemberScoreAppend]
GO

/****** Object:  StoredProcedure [dbo].[USP_MemberScoreValue_Update]    Script Date: 10/16/2011 05:48:48 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USP_MemberScoreValue_Update]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[USP_MemberScoreValue_Update]
GO

/****** Object:  StoredProcedure [dbo].[USP_MemberLevelInfo_Append]    Script Date: 10/16/2011 05:48:48 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USP_MemberLevelInfo_Append]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[USP_MemberLevelInfo_Append]
GO

/****** Object:  StoredProcedure [dbo].[USP_MemberInfo_Update]    Script Date: 10/16/2011 05:48:48 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USP_MemberInfo_Update]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[USP_MemberInfo_Update]
GO

/****** Object:  StoredProcedure [dbo].[USP_MemberLevelInfo_Update]    Script Date: 10/16/2011 05:48:48 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USP_MemberLevelInfo_Update]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[USP_MemberLevelInfo_Update]
GO

/****** Object:  StoredProcedure [dbo].[USP_MemberConsumeHistory_Year_Query]    Script Date: 10/16/2011 05:48:48 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USP_MemberConsumeHistory_Year_Query]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[USP_MemberConsumeHistory_Year_Query]
GO

/****** Object:  StoredProcedure [dbo].[USP_MemberConsumeHistory_Month_Query]    Script Date: 10/16/2011 05:48:48 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USP_MemberConsumeHistory_Month_Query]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[USP_MemberConsumeHistory_Month_Query]
GO

/****** Object:  StoredProcedure [dbo].[USP_MemberConsumeHistory_Day_Query]    Script Date: 10/16/2011 05:48:48 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USP_MemberConsumeHistory_Day_Query]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[USP_MemberConsumeHistory_Day_Query]
GO

/****** Object:  StoredProcedure [dbo].[USP_MemberConsumeHistory]    Script Date: 10/16/2011 05:48:48 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USP_MemberConsumeHistory]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[USP_MemberConsumeHistory]
GO

USE [eShop]
GO

/****** Object:  StoredProcedure [dbo].[USP_EmployeeInfo_Query]    Script Date: 10/16/2011 05:48:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[USP_EmployeeInfo_Query]
@EmployeeID	VARCHAR(12)=NULL
AS
IF @EmployeeID IS NULL
SELECT EmployeeID,FullName,Sex,Brithday,IsGregorianCalendar,LastLoginDatetime FROM T_Employee_Info
ELSE
SELECT * FROM T_Employee_Info WHERE EmployeeID=@EmployeeID






GO

/****** Object:  StoredProcedure [dbo].[USP_Add_EmployeeInfo]    Script Date: 10/16/2011 05:48:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[USP_Add_EmployeeInfo]	--T_Employee_Info
	@FullName				VARCHAR(50),
	@EmployeePID			VARCHAR(18),
	@Sex					BIT,
	@Brithday				DATETIME,
	@IsGregorianCalendar	BIT,
	@Popedom				VARCHAR(50),
	@Address				VARCHAR(255),
	@Mobile					VARCHAR(12),
	@Remark					TEXT
AS
DECLARE @EmployeeID		INT
IF NOT EXISTS(SELECT EmployeeID FROM T_Employee_Info WHERE EmployeePID=@EmployeePID)
BEGIN
INSERT INTO T_Employee_Info
(FullName,EmployeePID,Sex,Brithday,IsGregorianCalendar,Popedom,[Address],Mobile,Remark)
VALUES
(@FullName,@EmployeePID,@Sex,@Brithday,@IsGregorianCalendar,@Popedom,@Address,@Mobile,@Remark)

SET @EmployeeID=@@IDENTITY
SELECT EmployeeID,EmployeePWD,FullName,EmployeePID,Sex,Brithday,IsGregorianCalendar,Popedom,[Address],Mobile,Remark,CreateDatetime,EmployeePurview 
FROM T_Employee_Info WHERE ID=@EmployeeID
END
ELSE SELECT * FROM T_Employee_Info WHERE 1<>1



GO

/****** Object:  StoredProcedure [dbo].[USP_Add_MerchandiseBasicInfo]    Script Date: 10/16/2011 05:48:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[USP_Add_MerchandiseBasicInfo]	--T_Merchandise_BasicInfo/T_Merchandise_Sell
	@Barcode				VARCHAR(30),
	@MerchandiseClassID		Int,
	@MerchandiseName		VARCHAR(50),
	@MerchandiseUnitID		Int,
	@MerchandiseResume		TEXT,
	@MerchandiseRemark		TEXT,
	@IsAlert				BIT,
	@AlterNum				INT=NULL,
	@HasOverTime			BIT
AS
--BEGIN TRAN
IF EXISTS(SELECT * FROM T_Merchandise_BasicInfo WHERE Barcode=@Barcode)
BEGIN
UPDATE T_Merchandise_BasicInfo SET MerchandiseName='' WHERE 1<>1
END
ELSE
BEGIN
INSERT INTO T_Merchandise_BasicInfo
(Barcode,MerchandiseClassID,MerchandiseName,MerchandiseUnitID,MerchandiseResume,MerchandiseRemark,IsAlert,AlertNum,HasOverTime)
VALUES
(@Barcode,@MerchandiseClassID,@MerchandiseName,@MerchandiseUnitID,@MerchandiseResume,@MerchandiseRemark,@IsAlert,@AlterNum,@HasOverTime)
END

--IF @@ERROR=0 
--	BEGIN COMMIT TRAN
--	SET @Result=1
--	END
--ELSE 
--	BEGIN ROLLBACK TRAN END
RETURN 



GO

/****** Object:  StoredProcedure [dbo].[USP_EmployeeInfo_Update]    Script Date: 10/16/2011 05:48:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[USP_EmployeeInfo_Update]
@EmployeeID				VARCHAR(12),
@FullName				VARCHAR(50),
@Sex					BIT,
@EmployeePID			VARCHAR(18),
@Brithday				DATETIME,
@IsGregorianCalendar	BIT,
@Popedom				VARCHAR(50),
@Address				VARCHAR(255),
@Mobile					VARCHAR(12),
@Remark					TEXT
AS
UPDATE T_Employee_Info SET FullName=@FullName,
Sex=@Sex,
EmployeePID=@EmployeePID,
Brithday=@Brithday,
IsGregorianCalendar=@IsGregorianCalendar,
Popedom=@Popedom,
[Address]=@Address,
Mobile=@Mobile,
Remark=@Remark
WHERE EmployeeID=@EmployeeID

GO

/****** Object:  StoredProcedure [dbo].[USP_AppendMerchandiseClass]    Script Date: 10/16/2011 05:48:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


/*
瘛餃???蝐餃?
*/
CREATE PROCEDURE [dbo].[USP_AppendMerchandiseClass]
@MerchandiseClassName	VARCHAR(50),
@MerchandiseClassResume	TEXT,
@MerchandiseClassRemark TEXT
AS
DECLARE @MerchandiseClassID INT
IF EXISTS (SELECT MerchandiseClassName FROM T_Merchandise_Class WHERE MerchandiseClassName=@MerchandiseClassName)
BEGIN
SELECT * FROM T_Merchandise_Class WHERE 1<>1
END
ELSE
BEGIN
INSERT INTO T_Merchandise_Class(MerchandiseClassName,MerchandiseClassResume,MerchandiseClassRemark)
VALUES
(@MerchandiseClassName,@MerchandiseClassResume,@MerchandiseClassRemark)
SET @MerchandiseClassID=@@IDENTITY
SELECT MerchandiseClassID,MerchandiseClassName,MerchandiseClassResume,MerchandiseClassRemark FROM T_Merchandise_Class
WHERE MerchandiseClassID=@MerchandiseClassID
END


GO

/****** Object:  StoredProcedure [dbo].[USP_EmployeeLogin]    Script Date: 10/16/2011 05:48:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


/*
?∪極?駁?
餈??潘?
		0, ?駁?憭望?
		1, ?駁???
*/
Create PROCEDURE [dbo].[USP_EmployeeLogin] 
	@EmployeeID		VARCHAR(12),
	@EmployeePWD	VARCHAR(50)
AS
Set NoCount ON

 Select 
	ID,
	EmployeeID,
	FullName,
	LastLoginDatetime,
	EmployeePurview
 From T_Employee_Info
 Where EmployeeID=@EmployeeID AND EmployeePWD=@EmployeePWD

If(@@RowCount > 0)
 Update T_Employee_Info 
 Set LastLoginDatetime = GetDate()
 Where EmployeeID=@EmployeeID

RETURN 



GO

/****** Object:  StoredProcedure [dbo].[USP_GetSigleSellMerchandiseInfo]    Script Date: 10/16/2011 05:48:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--???嗥???靽⊥
Create Proc [dbo].[USP_GetSigleSellMerchandiseInfo]
(
	@BarCode varchar(30)
)
As

Set NoCount ON
SELECT S.[Barcode]
      ,S.[StockInOrder]
      ,S.[SellQuantity]
      ,S.[SellPrice]
      ,S.[SalePrice]
	  ,B.MerchandiseName
FROM [T_Merchandise_Sell] S
Left Join T_Merchandise_BasicInfo B On B.BarCode = S.BarCode
Where S.BarCode= @BarCode

Return


GO

/****** Object:  StoredProcedure [dbo].[USP_GetAllEmployeePermission]    Script Date: 10/16/2011 05:48:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--????撌亦???
Create Procedure [dbo].[USP_GetAllEmployeePermission]
As
Set NoCount ON

Select ID, EmployeeID,FullName, EmployeePurview
From T_Employee_Info 

Return


GO

/****** Object:  StoredProcedure [dbo].[USP_GetStockExamineList]    Script Date: 10/16/2011 05:48:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--??摨??圈?嚗??嗆??
CREATE Proc [dbo].[USP_GetStockExamineList]
(
	@BarCode Varchar(30)=NULL
)
As
Set NoCount ON

IF @BarCode IS NULL
	--Select
	--B.Barcode,B.MerchandiseName,U.UnitName,
	--Sum(S.StockInNum) As TotalStock,
	--Sum(Case S.IsStatu when 0 Then S.StockInNum Else 0 End) As InStock,
	--M.SellQuantity
	--From T_Merchandise_BasicInfo B
	--Left Join T_Stock_In S ON S.Barcode = B.Barcode
	--Left Join T_Merchandise_Sell M ON M.BarCode = B.Barcode
	--Left Join T_Merchandise_Unit U ON B.MerchandiseUnitID = U.UnitID
	--Group by B.Barcode,B.MerchandiseName,U.UnitName,M.SellQuantity
	

	select Barcode,MerchandiseName,UnitName,
	(InStock+SellQuantity) as TotalStock,
	InStock,SellQuantity 
	from (
	Select
	B.Barcode,B.MerchandiseName,U.UnitName,
	Sum(Case S.IsStatu when 0 Then S.StockInNum Else 0 End) As InStock,
	M.SellQuantity
	From T_Merchandise_BasicInfo B
	Left Join T_Stock_In S ON S.Barcode = B.Barcode
	Left Join T_Merchandise_Sell M ON M.BarCode = B.Barcode
	Left Join T_Merchandise_Unit U ON B.MerchandiseUnitID = U.UnitID
	Group by B.Barcode,B.MerchandiseName,U.UnitName,M.SellQuantity
	) c
	

Else
	--Select
	--B.Barcode,B.MerchandiseName,U.UnitName,
	--Sum(S.StockInNum) As TotalStock,
	--Sum(Case S.IsStatu when 0 Then S.StockInNum Else 0 End) As InStock,
	--M.SellQuantity
	--From T_Merchandise_BasicInfo B
	--Left Join T_Stock_In S ON S.Barcode = B.Barcode 
	--Left Join T_Merchandise_Sell M ON M.BarCode = B.Barcode
	--Left Join T_Merchandise_Unit U ON B.MerchandiseUnitID = U.UnitID
	--Where B.Barcode = @Barcode
	--Group by B.Barcode,B.MerchandiseName,U.UnitName,M.SellQuantity
	
	select Barcode,MerchandiseName,UnitName,
	(InStock+SellQuantity) as TotalStock,
	InStock,SellQuantity 
	from (
	Select
	B.Barcode,B.MerchandiseName,U.UnitName,
	Sum(Case S.IsStatu when 0 Then S.StockInNum Else 0 End) As InStock,
	M.SellQuantity
	From T_Merchandise_BasicInfo B
	Left Join T_Stock_In S ON S.Barcode = B.Barcode
	Left Join T_Merchandise_Sell M ON M.BarCode = B.Barcode
	Left Join T_Merchandise_Unit U ON B.MerchandiseUnitID = U.UnitID
	WHERE B.Barcode=@Barcode
	Group by B.Barcode,B.MerchandiseName,U.UnitName,M.SellQuantity
	) c
	

Return


GO

/****** Object:  StoredProcedure [dbo].[USP_GetStockToSellData]    Script Date: 10/16/2011 05:48:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- ??摨??唳嚗?憭???
Create Proc [dbo].[USP_GetStockToSellData]
As
Set NoCount ON

Select
S.StockInOrder, B.Barcode,B.MerchandiseName, S.StockInNum, S.UnitPrice, S.TotalPrice,
S.OverTime, S.StockInTime
From T_Stock_In S
Left Join T_Merchandise_BasicInfo B ON S.Barcode = B.Barcode
Where S.IsStatu = 0
Order by S.OverTime, S.StockInTime

Return


GO

/****** Object:  StoredProcedure [dbo].[USP_Merchandise_BasicInfo_Delete]    Script Date: 10/16/2011 05:48:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--?銝?∟扇敶?
CREATE PROCEDURE [dbo].[USP_Merchandise_BasicInfo_Delete]
(
	@Barcode varchar(30)
)
 AS 
	DELETE [T_Merchandise_BasicInfo] WHERE Barcode=@Barcode 



GO

/****** Object:  StoredProcedure [dbo].[USP_Merchandise_BasicInfo_GetAllList]    Script Date: 10/16/2011 05:48:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--?亥砭??扇敶縑??
CREATE PROCEDURE [dbo].[USP_Merchandise_BasicInfo_GetAllList]
@Barcode varchar(30)=null
 AS 
 IF @Barcode IS NULL
	SELECT 
	M.Barcode,M.MerchandiseClassID,C.MerchandiseClassName,M.MerchandiseName,M.MerchandiseUnitID, U.UnitName, M.MerchandiseResume, M.MerchandiseRemark,
	M.IsAlert,M.AlertNum, M.HasOverTime
	FROM [T_Merchandise_BasicInfo] M
    Left Join T_Merchandise_Unit U ON U.UnitID = M.MerchandiseUnitID
	Left Join T_Merchandise_Class C ON M.MerchandiseClassID = C.MerchandiseClassID
ELSE 
	SELECT 
	M.Barcode,M.MerchandiseClassID,C.MerchandiseClassName,M.MerchandiseName,M.MerchandiseUnitID, U.UnitName, M.MerchandiseResume, M.MerchandiseRemark,
	M.IsAlert,M.AlertNum, M.HasOverTime
	FROM [T_Merchandise_BasicInfo] M
    Left Join T_Merchandise_Unit U ON U.UnitID = M.MerchandiseUnitID
	Left Join T_Merchandise_Class C ON M.MerchandiseClassID = C.MerchandiseClassID
	WHERE M.Barcode=@Barcode




GO

/****** Object:  StoredProcedure [dbo].[USP_Merchandise_BasicInfo_Update]    Script Date: 10/16/2011 05:48:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



--靽格銝?∟扇敶?
CREATE PROCEDURE [dbo].[USP_Merchandise_BasicInfo_Update]
(
	@Barcode varchar(30),
	@MerchandiseClassID int,
	@MerchandiseName varchar(50),
	@MerchandiseUnitID char(2),
	@MerchandiseResume text,
	@MerchandiseRemark text,
	@IsAlert bit,
	@AlertNum int,
	@HasOverTime bit,
	@OldBarCode varchar(30)
)

 AS 
	UPDATE [T_Merchandise_BasicInfo] 
	SET 
	Barcode = @Barcode,
	[MerchandiseClassID] = @MerchandiseClassID,[MerchandiseName] = @MerchandiseName,[MerchandiseUnitID] = @MerchandiseUnitID,[MerchandiseResume] = @MerchandiseResume,[MerchandiseRemark] = @MerchandiseRemark,[IsAlert] = @IsAlert,[AlertNum] = @AlertNum,[HasOverTime] = @HasOverTime
	WHERE Barcode=@OldBarCode 



GO

/****** Object:  StoredProcedure [dbo].[USP_Merchandise_Order_ADD]    Script Date: 10/16/2011 05:48:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--?霈Ｗ?嚗鈭?嗥?韐?
Create Proc [dbo].[USP_Merchandise_Order_ADD]
(
	@MemberID varchar(20),
	@PayAmount money,
	@Discount decimal(3,2),
	@RealAmount money,
	@EmployeeID varchar(30),
	@OrderRemark text=NuLL
)
As

Insert Into
T_Merchandise_Order(PayAmount,MemberID,Discount,RealAmount,EmployeeID,OrderRemark,SellTime)
values(@PayAmount,@MemberID,@Discount,@RealAmount,@EmployeeID,@OrderRemark, GetDate())

Select @@identity

Return



GO

/****** Object:  StoredProcedure [dbo].[USP_Merchandise_Class_GetList]    Script Date: 10/16/2011 05:48:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


------------------------------------
--?券??亥砭???掩??扇敶縑??
------------------------------------
CREATE PROCEDURE [dbo].[USP_Merchandise_Class_GetList]
 AS 

SELECT [MerchandiseClassID]
      ,[MerchandiseClassName]
      ,[MerchandiseClassResume]
      ,[MerchandiseClassRemark]
  FROM [T_Merchandise_Class]



GO

/****** Object:  StoredProcedure [dbo].[USP_Merchandise_Sell_Query]    Script Date: 10/16/2011 05:48:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[USP_Merchandise_Sell_Query]
@Barcode	VARCHAR(30)
AS
select S.Barcode,S.StockInOrder,S.SellPrice,S.SalePrice,S.SellQuantity,B.MerchandiseName,U.UnitName,S.SellTime
from T_Merchandise_Sell S,T_Merchandise_BasicInfo B,T_Merchandise_Unit U
where S.Barcode=B.Barcode
AND B.MerchandiseUnitID=U.UnitID
AND S.Barcode=@Barcode


GO

/****** Object:  StoredProcedure [dbo].[USP_Merchandise_OrderDetail_ADD]    Script Date: 10/16/2011 05:48:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--?霈Ｗ?銝剛祕蝏縑?荔??其??園
Create Proc [dbo].[USP_Merchandise_OrderDetail_ADD]
(
	@SerialNumber varchar(20),
	@OrderID Int,
	@Barcode varchar(30),
	@StockInOrder int,
	@BuyNumber int,
	@SellPrice money,
	@SaleAmount money
)
As

Insert Into T_Merchandise_OrderDetail(SerialNumber,OrderID,Barcode,StockInOrder,BuyNumber,SellPrice,SaleAmount,SaleTime)
Values(@SerialNumber,@OrderID,@Barcode,@StockInOrder,@BuyNumber,@SellPrice,@SaleAmount,GetDate())

Return


GO

/****** Object:  StoredProcedure [dbo].[USP_MerchandiseSee_UpdateQuantity]    Script Date: 10/16/2011 05:48:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[USP_MerchandiseSee_UpdateQuantity]
(	
	@Barcode	VARCHAR(30),
	@Num		INT
)
AS
UPDATE T_Merchandise_Sell SET SellQuantity=SellQuantity+@Num WHERE Barcode=@Barcode


GO

/****** Object:  StoredProcedure [dbo].[USP_Merchandise_Unit_GetList]    Script Date: 10/16/2011 05:48:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


------------------------------------
--?券??亥砭????扇敶縑??
------------------------------------
CREATE PROCEDURE [dbo].[USP_Merchandise_Unit_GetList]
 AS 
	SELECT 
	UnitID,UnitName
	 FROM [T_Merchandise_Unit]



GO

/****** Object:  StoredProcedure [dbo].[USP_MerchandiseSell_ADD]    Script Date: 10/16/2011 05:48:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--?銝?唳
Create PROC [dbo].[USP_MerchandiseSell_ADD]
(
	@Barcode varchar(30),
	@StockInOrder int,
	@SellQuantity int,
	@SellPrice money,
	@SalePrice money
)
As

Begin Tran
	Insert Into T_Merchandise_Sell
	(Barcode,StockInOrder,SellQuantity,SellPrice,SalePrice,SellTime)
	Values(@Barcode,@StockInOrder,@SellQuantity,@SellPrice,@SalePrice,GetDate())
	

	if @@RowCount >0
		Update T_Stock_In Set IsStatu = 1 Where StockInOrder = @StockInOrder

	IF @@ERROR=0
		COMMIT TRAN
	Else
		ROLLBACK TRAN 

Return


GO

/****** Object:  StoredProcedure [dbo].[USP_MerchandiseSell_Query]    Script Date: 10/16/2011 05:48:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--?亥砭銝???縑??
CREATE PROCEDURE [dbo].[USP_MerchandiseSell_Query]
@Barcode	varchar(30)=null
as
if @Barcode is null
select B.Barcode,B.MerchandiseName,S.SellPrice,S.SalePrice FROM T_Merchandise_BasicInfo B LEFT JOIN T_Merchandise_Sell S
ON B.Barcode=S.Barcode
else
select B.Barcode,B.MerchandiseName,S.SellPrice,S.SalePrice FROM T_Merchandise_BasicInfo B LEFT JOIN T_Merchandise_Sell S
ON B.Barcode=S.Barcode
WHERE B.Barcode=@Barcode


GO

/****** Object:  StoredProcedure [dbo].[USP_MerchandiseSell_ToSell]    Script Date: 10/16/2011 05:48:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[USP_MerchandiseSell_ToSell]
@Barcode	varchar(30),
@SellPrice	money,
@SalePrice	money
as
if exists (select * from T_Merchandise_Sell where Barcode=@Barcode)
begin
update T_Merchandise_Sell set SellPrice=@SellPrice,SalePrice=@SalePrice where @Barcode=Barcode
end
else
begin
insert into T_Merchandise_Sell(Barcode,SellPrice,SalePrice,SellTime) values(@Barcode,@SellPrice,@SalePrice,GETDATE())
end

GO

/****** Object:  StoredProcedure [dbo].[USP_QueryMerchandiseClass]    Script Date: 10/16/2011 05:48:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[USP_QueryMerchandiseClass]
@MerchandiseClassID INT =NULL
AS
IF @MerchandiseClassID IS NULL
BEGIN
SELECT MerchandiseClassID ,MerchandiseClassName FROM T_Merchandise_Class
END
ELSE
BEGIN
SELECT MerchandiseClassName,MerchandiseClassResume,MerchandiseClassRemark FROM T_Merchandise_Class
WHERE MerchandiseClassID=@MerchandiseClassID
END


GO

/****** Object:  StoredProcedure [dbo].[USP_Search_MerchandiseBasicInfo]    Script Date: 10/16/2011 05:48:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


Create Proc [dbo].[USP_Search_MerchandiseBasicInfo]
(
	@SearchValue Varchar(200),
	@MerchandiseClassID INT = 0
)
As

If @MerchandiseClassID = 0
	SELECT 
	M.Barcode,M.MerchandiseClassID,C.MerchandiseClassName,M.MerchandiseName,M.MerchandiseUnitID, U.UnitName, M.MerchandiseResume, M.MerchandiseRemark,
	M.IsAlert,M.AlertNum, M.HasOverTime
	FROM [T_Merchandise_BasicInfo] M
	Left Join T_Merchandise_Unit U ON U.UnitID = M.MerchandiseUnitID
	Left Join T_Merchandise_Class C ON M.MerchandiseClassID = C.MerchandiseClassID
	Where M.Barcode Like '%'+@SearchValue+'%' Or M.MerchandiseName Like '%'+@SearchValue+'%'
Else
	SELECT 
	M.Barcode,M.MerchandiseClassID,C.MerchandiseClassName,M.MerchandiseName,M.MerchandiseUnitID, U.UnitName, M.MerchandiseResume, M.MerchandiseRemark,
	M.IsAlert,M.AlertNum, M.HasOverTime
	FROM [T_Merchandise_BasicInfo] M
    Left Join T_Merchandise_Unit U ON U.UnitID = M.MerchandiseUnitID
	Left Join T_Merchandise_Class C ON M.MerchandiseClassID = C.MerchandiseClassID
	Where (M.Barcode Like '%'+@SearchValue+'%' Or M.MerchandiseName Like '%'+@SearchValue+'%') And M.MerchandiseClassID = @MerchandiseClassID

RETURN




GO

/****** Object:  StoredProcedure [dbo].[USP_StockIn_Append]    Script Date: 10/16/2011 05:48:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[USP_StockIn_Append]		--T_Stock_In
	@Barcode		VARCHAR(30),
	@StockInNum		INT,
	@UnitPrice		MONEY,
	@TotalPrice		MONEY,
	@OverTime		DATETIME
AS
INSERT INTO T_Stock_In
(Barcode,StockInNum,UnitPrice,TotalPrice,OverTime)
VALUES
(@Barcode,@StockInNum,@UnitPrice,@TotalPrice,@OverTime)




GO

/****** Object:  StoredProcedure [dbo].[USP_UpdateEmployeePermission]    Script Date: 10/16/2011 05:48:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--霈曄蔭?詨??極????
Create Procedure [dbo].[USP_UpdateEmployeePermission]
(
	@EmployeeID int,
	@EmployeePermission int
)
As

Update T_Employee_Info
Set EmployeePurview = @EmployeePermission
Where ID = @EmployeeID

Return


GO

/****** Object:  StoredProcedure [dbo].[USP_Update_MerchandiseSell]    Script Date: 10/16/2011 05:48:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[USP_Update_MerchandiseSell]
	@Barcode		VARCHAR(30),	--T_Merchandise_Sell
	@Result			INT =0 OUTPUT
AS
DECLARE @StockInOrder		INT
DECLARE @SellQuantity		INT
DECLARE @OldStockInOrder	INT
DECLARE @SellTime			DATETIME

IF EXISTS (SELECT Barcode FROM T_Merchandise_Sell WHERE Barcode=@Barcode)
BEGIN
SELECT @StockInOrder = MIN(StockInOrder) FROM T_Stock_In WHERE Barcode=@Barcode AND IsStatu=0
IF @StockInOrder IS NULL 
BEGIN
	SET @Result=2	
END
ELSE
	BEGIN
	SELECT @SellQuantity = StockInNum FROM T_Stock_In WHERE StockInOrder=@StockInOrder
	SELECT @OldStockInOrder = StockInOrder FROM T_Stock_In WHERE Barcode=@Barcode AND IsStatu=1
	SET @SellTime=GETDATE()
	UPDATE T_Stock_In SET IsStatu=2 WHERE StockInOrder=@OldStockInOrder --撠歇?瑕摰??寞活?典摨怨”銝剜?寧??撌脣摰2]
	UPDATE T_Stock_In SET IsStatu=1 WHERE StockInOrder=@StockInOrder	--?府???撠????芯??跋0]?摨急甈∟?嚗??嗥???啁撌脖??嗥??1]
	UPDATE T_Merchandise_Sell SET StockInOrder=@StockInOrder,SellQuantity=@SellQuantity,SellTime=@SellTime
		WHERE Barcode=@Barcode											--?寞????甈∟?撠府?寞活?縑?舀?啣銝銵其葉[T_Merchandise_Sell]
	
	IF @@ERROR=0
		SET @Result=1
		RETURN @Result
		END
END
ELSE
BEGIN 
SET @Result=3--?芯???
RETURN @Result
END


GO

/****** Object:  StoredProcedure [dbo].[USP_UpdateMerchandiseClass]    Script Date: 10/16/2011 05:48:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[USP_UpdateMerchandiseClass]
@MerchandiseClassID		INT,
@MerchandiseClassName	VARCHAR(50),
@MerchandiseClassResume	TEXT,
@MerchandiseClassRemark	TEXT
AS
UPDATE T_Merchandise_Class SET MerchandiseClassName=@MerchandiseClassName,MerchandiseClassResume=@MerchandiseClassResume,MerchandiseClassRemark=@MerchandiseClassRemark
WHERE MerchandiseClassID=@MerchandiseClassID


GO

/****** Object:  StoredProcedure [dbo].[USP_MerchandiseAlterStatus_Update]    Script Date: 10/16/2011 05:48:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[USP_MerchandiseAlterStatus_Update]
@Barcode	VARCHAR(30),
@IsAlert	BIT,
@AlertNum	INT
AS
UPDATE T_Merchandise_BasicInfo
	SET IsAlert=@IsAlert,AlertNum=@AlertNum
	WHERE Barcode=@Barcode
GO

/****** Object:  StoredProcedure [dbo].[USP_MerchandiseUnit_Append]    Script Date: 10/16/2011 05:48:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[USP_MerchandiseUnit_Append]
@UnitName	VARCHAR(50)
AS
IF EXISTS (SELECT UnitName FROM T_Merchandise_Unit WHERE UnitName=@UnitName)
UPDATE T_Merchandise_Unit SET UnitName=@UnitName WHERE 1<>1
ELSE
INSERT INTO T_Merchandise_Unit(UnitName) VALUES(@UnitName)

GO

/****** Object:  StoredProcedure [dbo].[USP_MerchandiseTodaySelled_Query]    Script Date: 10/16/2011 05:48:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[USP_MerchandiseTodaySelled_Query]
@EmployeeID	VARCHAR(12)=NULL,
@SellDate	DATETIME=NULL
AS
IF @SellDate IS NULL SET @SellDate=GETDATE()
IF @EmployeeID IS NULL
BEGIN
--每日总营业额
SELECT SUM(CASE WHEN CONVERT(DATE,SellTime)=CONVERT(DATE,@SellDate) THEN RealAmount ELSE 0 END) AS RealAmount FROM T_Merchandise_Order
--每日销售清单
SELECT M.Barcode,M.MerchandiseName,SUM(MO.BuyNumber) BuyNumber FROM T_Merchandise_BasicInfo M,T_Merchandise_OrderDetail MO
WHERE MO.OrderID IN (SELECT DISTINCT OrderID FROM T_Merchandise_Order WHERE CONVERT(DATE,SellTime)=CONVERT(DATE,@SellDate))
AND M.Barcode=MO.Barcode
GROUP BY M.Barcode,M.MerchandiseName
ORDER BY BuyNumber DESC
END
ELSE
BEGIN
--每日指定员工销售额
SELECT SUM(CASE WHEN CONVERT(DATE,SellTime)=CONVERT(DATE,@SellDate) AND EmployeeID=@EmployeeID THEN RealAmount ELSE 0 END) AS RealAmount FROM T_Merchandise_Order
--每日指定员工销售清单
SELECT M.Barcode,M.MerchandiseName,SUM(MO.BuyNumber) BuyNumber FROM T_Merchandise_BasicInfo M,T_Merchandise_OrderDetail MO
WHERE MO.OrderID IN (SELECT DISTINCT OrderID FROM T_Merchandise_Order WHERE EmployeeID=@EmployeeID AND CONVERT(DATE,SellTime)=CONVERT(DATE,@SellDate))
AND M.Barcode=MO.Barcode
GROUP BY M.Barcode,M.MerchandiseName
ORDER BY BuyNumber DESC
END


GO

/****** Object:  StoredProcedure [dbo].[USP_MerchandiseAlterNumCheck]    Script Date: 10/16/2011 05:48:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[USP_MerchandiseAlterNumCheck]
@Type	INT=0,--0,检测濒临低量的商品;1,检测所有设置低量报警的商品;2,检测指定Barcode的商品
@Barcode VARCHAR(30)=NULL
AS
DECLARE @SQL VARCHAR(8000)
SET @SQL='SELECT * FROM ('
SET @SQL+='	select Barcode,MerchandiseName,UnitName,'
SET @SQL+='	(InStock+SellQuantity) as TotalStock,'
SET @SQL+='	IsAlert,AlertNum'
SET @SQL+='	from ('
SET @SQL+='	Select'
SET @SQL+='	B.Barcode,B.MerchandiseName,B.IsAlert,B.AlertNum,U.UnitName,'
SET @SQL+='	Sum(Case S.IsStatu when 0 Then S.StockInNum Else 0 End) As InStock,'
SET @SQL+='	M.SellQuantity'
SET @SQL+='	From T_Merchandise_BasicInfo B'
SET @SQL+='	Left Join T_Stock_In S ON S.Barcode = B.Barcode'
SET @SQL+='	Left Join T_Merchandise_Sell M ON M.BarCode = B.Barcode'
SET @SQL+='	Left Join T_Merchandise_Unit U ON B.MerchandiseUnitID = U.UnitID'
IF @Type=0 OR @Type=1
	SET @SQL+='	WHERE B.IsAlert=1'
IF @Type=2
	SET @SQL+='	WHERE B.Barcode='''+@Barcode+''''
SET @SQL+='	Group by B.Barcode,B.MerchandiseName,B.IsAlert,B.AlertNum,U.UnitName,M.SellQuantity	) c'
SET @SQL+='	) D'
IF @Type=0
	SET @SQL+='	WHERE AlertNum>=TotalStock '
EXECUTE (@SQL)

GO

/****** Object:  StoredProcedure [dbo].[USP_MerchandiseOverTimeCheck]    Script Date: 10/16/2011 05:48:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[USP_MerchandiseOverTimeCheck]
@AlertEarlierDay INT =10
AS
SELECT S.StockInOrder,S.Barcode,B.MerchandiseName,S.StockInNum,S.IsStatu,S.OverTime,S.StockInTime FROM T_Stock_In S,T_Merchandise_BasicInfo B
	WHERE CONVERT(DATE,S.OverTime)<=DATEADD(DD,10,CONVERT(DATE,GETDATE()))
	AND IsStatu <>2
	AND S.Barcode IN(SELECT Barcode FROM T_Merchandise_BasicInfo WHERE HasOverTime=1)
	AND S.Barcode=B.Barcode


GO

/****** Object:  StoredProcedure [dbo].[USP_SelledEmployee]    Script Date: 10/16/2011 05:48:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[USP_SelledEmployee]
@QueryDate	DATETIME
AS
SELECT DISTINCT O.EmployeeID,E.FullName FROM T_Merchandise_Order O,T_Employee_Info E
WHERE CONVERT(DATE,O.SellTime)=CONVERT(DATE,@QueryDate)
AND O.EmployeeID=E.EmployeeID


GO

/****** Object:  StoredProcedure [dbo].[USP_TrendChart]    Script Date: 10/16/2011 05:48:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[USP_TrendChart]
@Type INT=0,
@BeginDate DATETIME,
@EndDate DATETIME
AS
IF @Type=0 --日趋势
	BEGIN
			
	SELECT A.SellTime,B.BuyNumber,A.RealAmount FROM 
			(
			SELECT 	CONVERT(VARCHAR(10),CONVERT(DATE,O.SellTime)) SellTime,	SUM(O.RealAmount) RealAmount
				FROM T_Merchandise_Order O
				WHERE YEAR(SellTime) BETWEEN YEAR(@BeginDate) AND YEAR(@EndDate)
				AND MONTH(SellTime) BETWEEN MONTH(@BeginDate) AND MONTH(@EndDate)
				AND DAY(SellTime) BETWEEN DAY(@BeginDate) AND DAY(@EndDate)
				GROUP BY CONVERT(VARCHAR(10),CONVERT(DATE,O.SellTime))) A,
			(
			SELECT CONVERT(VARCHAR(10),CONVERT(DATE,SaleTime)) SellTime,SUM(BuyNumber) BuyNumber
				FROM T_Merchandise_OrderDetail
				WHERE YEAR(SaleTime) BETWEEN YEAR(@BeginDate) AND YEAR(@EndDate)
				AND MONTH(SaleTime) BETWEEN MONTH(@BeginDate) AND MONTH(@EndDate)
				AND DAY(SaleTime) BETWEEN DAY(@BeginDate) AND DAY(@EndDate)
				GROUP BY CONVERT(VARCHAR(10),CONVERT(DATE,SaleTime))) B
		WHERE A.SellTime=B.SellTime	
		
	END
ELSE IF @Type=1--月趋势
	BEGIN
	DECLARE @tmpBegin varchar(20),@tmpEnd varchar(20)
	SET @tmpBegin=CONVERT(VARCHAR(4),YEAR(@BeginDate))+'-'+CONVERT(VARCHAR(2),MONTH(@BeginDate))+'-1 00:00:00'
	SET @tmpEnd=CONVERT(VARCHAR(4),YEAR(@EndDate))+'-'+CONVERT(VARCHAR(2),MONTH(@EndDate))+'-31 23:59:59'
			SELECT A.SellTime,B.BuyNumber,A.RealAmount FROM 
			(
			SELECT 	CONVERT(VARCHAR(4),YEAR(O.SellTime))+'-'+CONVERT(VARCHAR(2),MONTH(O.SellTime)) SellTime,SUM(O.RealAmount) RealAmount
				FROM T_Merchandise_Order O
				WHERE SellTime BETWEEN @tmpBegin AND @tmpEnd
				GROUP BY CONVERT(VARCHAR(4),YEAR(O.SellTime))+'-'+CONVERT(VARCHAR(2),MONTH(O.SellTime))) A,
			(
			SELECT CONVERT(VARCHAR(4),YEAR(SaleTime))+'-'+CONVERT(VARCHAR(2),MONTH(SaleTime)) SellTime,SUM(BuyNumber) BuyNumber
				FROM T_Merchandise_OrderDetail
				WHERE SaleTime BETWEEN @tmpBegin AND @tmpEnd
				GROUP BY CONVERT(VARCHAR(4),YEAR(SaleTime))+'-'+CONVERT(VARCHAR(2),MONTH(SaleTime))) B
		WHERE A.SellTime=B.SellTime	
		
	END
GO

/****** Object:  StoredProcedure [dbo].[USP_HotMerchandiseQuery]    Script Date: 10/16/2011 05:48:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[USP_HotMerchandiseQuery]
@BeginDate	DATETIME,
@EndDate DATETIME
AS
SELECT B.Barcode,B.MerchandiseName,SUM(OD.BuyNumber) Buynumber
	FROM T_Merchandise_BasicInfo B,T_Merchandise_OrderDetail OD
	WHERE OD.Barcode=B.Barcode
	AND CONVERT(DATE,OD.SaleTime) BETWEEN CONVERT(DATE,@BeginDate) AND CONVERT(DATE,@EndDate)
	GROUP BY B.Barcode,B.MerchandiseName
	ORDER BY Buynumber DESC
	
	--EXEC  USP_HotMerchandiseQuery '2011-10-01 07:30','2011-10-30 07:30'
GO

/****** Object:  StoredProcedure [dbo].[USP_MemberQuery]    Script Date: 10/16/2011 05:48:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[USP_MemberQuery]
@MemberID VARCHAR(20)=NULL,
@Type INT=0
AS
IF @MemberID IS NULL
	SELECT B.MemberID,B.MemberName,MemberSex,B.Brithday,B.IsGregorianCalendar,B.MemberAddress,
	B.MemberMobel,B.LevelID,L.LevelName,L.LevelDiscount,V.MemberScoreValue,B.[Type],B.Remark
		FROM T_Member_BasicInfo B,T_Level_Info L,T_Member_Score_Value V
		WHERE B.LevelID=L.LevelID
		AND B.MemberID=V.MemberID
		AND B.[Type] IN(@Type)
ELSE
	SELECT B.MemberID,B.MemberName,MemberSex,B.Brithday,B.IsGregorianCalendar,B.MemberAddress,
	B.MemberMobel,B.LevelID,L.LevelName,L.LevelDiscount,V.MemberScoreValue,B.[Type],B.Remark
		FROM T_Member_BasicInfo B,T_Level_Info L,T_Member_Score_Value V
		WHERE B.LevelID=L.LevelID
		AND B.MemberID=V.MemberID
		AND B.MemberID=@MemberID
GO

/****** Object:  StoredProcedure [dbo].[USP_MemberInfo_Append]    Script Date: 10/16/2011 05:48:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[USP_MemberInfo_Append]
@MemberID	VARCHAR(20),
@MemberName VARCHAR(50),
@MemberSex bit,
@Brithday datetime,
@IsGregorianCalendar bit,
@MemberAddress varchar(255),
@MemberMobel varchar(12),
@LevelID int,
@Remark text
AS
IF EXISTS (SELECT MemberID FROM T_Member_BasicInfo WHERE MemberID=@MemberID)
 UPDATE T_Member_BasicInfo SET MemberID=0 WHERE 1<>1
 ELSE
insert into T_Member_BasicInfo(MemberID,MemberName,MemberSex,Brithday,IsGregorianCalendar,MemberAddress,MemberMobel,LevelID,Remark)
	VALUES(@MemberID,@MemberName,@MemberSex,@Brithday,@IsGregorianCalendar,@MemberAddress,@MemberMobel,@LevelID,@Remark)
GO

/****** Object:  StoredProcedure [dbo].[USP_MemberLevelInfo_Query]    Script Date: 10/16/2011 05:48:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[USP_MemberLevelInfo_Query]
AS
SELECT LevelID,LevelName,LevelDiscount FROM T_Level_Info ORDER BY LevelDiscount DESC
GO

/****** Object:  StoredProcedure [dbo].[USP_MemberScoreAppend]    Script Date: 10/16/2011 05:48:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[USP_MemberScoreAppend]
@MemberID		VARCHAR(20),
@MemberScoreValue	INT,
@IsLimitTime	BIT,
@LasLimitTime	DATETIME
AS
INSERT INTO T_Member_Score_Value(MemberID,MemberScoreValue,IsLimitTime,LastLimitTime)
	VALUES(@MemberID,@MemberScoreValue,@IsLimitTime,@LasLimitTime)
	

GO

/****** Object:  StoredProcedure [dbo].[USP_MemberScoreValue_Update]    Script Date: 10/16/2011 05:48:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[USP_MemberScoreValue_Update]
@MemberID	VARCHAR(20),
@ScoreValue	INT
AS
UPDATE T_Member_Score_Value SET MemberScoreValue=MemberScoreValue+@ScoreValue
	WHERE MemberID=@MemberID

GO

/****** Object:  StoredProcedure [dbo].[USP_MemberLevelInfo_Append]    Script Date: 10/16/2011 05:48:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[USP_MemberLevelInfo_Append]
@LevelName	VARCHAR(50),
@LevelDiscount DECIMAL(3,2)
AS
INSERT INTO T_Level_Info(LevelName,LevelDiscount)
	VALUES(@LevelName,@LevelDiscount)
	
GO

/****** Object:  StoredProcedure [dbo].[USP_MemberInfo_Update]    Script Date: 10/16/2011 05:48:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[USP_MemberInfo_Update]
@MemberID	VARCHAR(20),
@MemberName VARCHAR(50),
@MemberSex bit,
@Brithday datetime,
@IsGregorianCalendar bit,
@MemberAddress varchar(255),
@MemberMobel varchar(12),
@LevelID int,
@Remark text
AS
UPDATE T_Member_BasicInfo SET MemberName=@MemberName,
							MemberSex=@MemberSex,
							Brithday=@Brithday,
							IsGregorianCalendar=@IsGregorianCalendar,
							MemberAddress=@MemberAddress,
							MemberMobel=@MemberMobel,
							LevelID=@LevelID,
							Remark=@Remark
			WHERE MemberID=@MemberID
GO

/****** Object:  StoredProcedure [dbo].[USP_MemberLevelInfo_Update]    Script Date: 10/16/2011 05:48:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[USP_MemberLevelInfo_Update]
@LevelID	INT,
@LevelName	VARCHAR(50),
@LevelDiscount DECIMAL(3,2)
AS
UPDATE T_Level_Info SET LevelName=@LevelName,LevelDiscount=@LevelDiscount WHERE LevelID=@LevelID
GO

/****** Object:  StoredProcedure [dbo].[USP_MemberConsumeHistory_Year_Query]    Script Date: 10/16/2011 05:48:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--SELECT DISTINCT YEAR(SellTime) FROM T_Merchandise_Order
--SELECT DISTINCT MONTH(SellTime) FROM T_Merchandise_Order
--SELECT DISTINCT DAY(SellTime) FROM T_Merchandise_Order

CREATE PROCEDURE [dbo].[USP_MemberConsumeHistory_Year_Query]
@MemberID VARCHAR(20)
AS
SELECT DISTINCT YEAR(SellTime) SellTime FROM T_Merchandise_Order
	WHERE MemberID=@MemberID

GO

/****** Object:  StoredProcedure [dbo].[USP_MemberConsumeHistory_Month_Query]    Script Date: 10/16/2011 05:48:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[USP_MemberConsumeHistory_Month_Query]
@MemberID VARCHAR(20),
@Year VARCHAR(4)
AS
SELECT DISTINCT MONTH(SellTime) SellTime FROM T_Merchandise_Order
	WHERE MemberID=@MemberID
	AND YEAR(SellTime)=@Year

GO

/****** Object:  StoredProcedure [dbo].[USP_MemberConsumeHistory_Day_Query]    Script Date: 10/16/2011 05:48:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[USP_MemberConsumeHistory_Day_Query]
@MemberID VARCHAR(20),
@Year VARCHAR(4),
@Month	VARCHAR(2)
AS
SELECT DISTINCT DAY(SellTime) SellTime FROM T_Merchandise_Order
	WHERE MemberID=@MemberID
	AND YEAR(SellTime)=@Year
	AND MONTH(SellTime)=@Month
	
GO

/****** Object:  StoredProcedure [dbo].[USP_MemberConsumeHistory]    Script Date: 10/16/2011 05:48:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[USP_MemberConsumeHistory]
@MemberID varchar(20),
@Type	INT,
@Year varchar(4)=NULL,
@Month varchar(2)=NULL,
@Day varchar(2)=NULL
AS
IF @Type=0
BEGIN
SELECT SUM(RealAmount) RealAmount FROM T_Merchandise_Order
	WHERE MemberID=@MemberID
	AND YEAR(SellTime)=@Year

SELECT B.Barcode,C.MerchandiseClassName,B.MerchandiseName,U.UnitName,SUM(MO.BuyNumber) BuyNumber 
			FROM T_Merchandise_OrderDetail MO,
				T_Merchandise_BasicInfo B,
				T_Merchandise_Class C,
				T_Merchandise_Unit U
			WHERE OrderID IN(
						SELECT DISTINCT OrderID FROM T_Merchandise_Order
							WHERE MemberID=@MemberID
							AND YEAR(SellTime)=@Year
							)
	AND B.Barcode=MO.Barcode
	AND B.MerchandiseClassID=C.MerchandiseClassID
	AND B.MerchandiseUnitID=U.UnitID
	GROUP BY B.Barcode,C.MerchandiseClassName,B.MerchandiseName,U.UnitName
END
IF @Type=1
BEGIN
SELECT SUM(RealAmount) RealAmount FROM T_Merchandise_Order
	WHERE MemberID=@MemberID
	AND YEAR(SellTime)=@Year
	AND MONTH(SellTime)=@Month
SELECT B.Barcode,C.MerchandiseClassName,B.MerchandiseName,U.UnitName,SUM(MO.BuyNumber) BuyNumber 
			FROM T_Merchandise_OrderDetail MO,
				T_Merchandise_BasicInfo B,
				T_Merchandise_Class C,
				T_Merchandise_Unit U
			WHERE OrderID IN(
						SELECT DISTINCT OrderID FROM T_Merchandise_Order
							WHERE MemberID=@MemberID
							AND YEAR(SellTime)=@Year
							AND MONTH(SellTime)=@Month
							)
	AND B.Barcode=MO.Barcode
	AND B.MerchandiseClassID=C.MerchandiseClassID
	AND B.MerchandiseUnitID=U.UnitID
	GROUP BY B.Barcode,C.MerchandiseClassName,B.MerchandiseName,U.UnitName
END
IF @Type=2
BEGIN
SELECT SUM(RealAmount) RealAmount FROM T_Merchandise_Order
	WHERE MemberID=@MemberID
	AND YEAR(SellTime)=@Year
	AND MONTH(SellTime)=@Month
	AND DAY(SellTime)=@Day
	
SELECT B.Barcode,C.MerchandiseClassName,B.MerchandiseName,U.UnitName,SUM(MO.BuyNumber) BuyNumber 
			FROM T_Merchandise_OrderDetail MO,
				T_Merchandise_BasicInfo B,
				T_Merchandise_Class C,
				T_Merchandise_Unit U
			WHERE OrderID IN(
						SELECT DISTINCT OrderID FROM T_Merchandise_Order
							WHERE MemberID=@MemberID
							AND YEAR(SellTime)=@Year
							AND MONTH(SellTime)=@Month
							AND DAY(SellTime)=@Day
							)
	AND B.Barcode=MO.Barcode
	AND B.MerchandiseClassID=C.MerchandiseClassID
	AND B.MerchandiseUnitID=U.UnitID
	GROUP BY B.Barcode,C.MerchandiseClassName,B.MerchandiseName,U.UnitName
END
IF @Type=3
BEGIN
SELECT SUM(RealAmount) RealAmount FROM T_Merchandise_Order
	WHERE MemberID=@MemberID
SELECT B.Barcode,C.MerchandiseClassName,B.MerchandiseName,U.UnitName,SUM(MO.BuyNumber) BuyNumber 
			FROM T_Merchandise_OrderDetail MO,
				T_Merchandise_BasicInfo B,
				T_Merchandise_Class C,
				T_Merchandise_Unit U
			WHERE OrderID IN(
						SELECT DISTINCT OrderID FROM T_Merchandise_Order
							WHERE MemberID=@MemberID
							)
	AND B.Barcode=MO.Barcode
	AND B.MerchandiseClassID=C.MerchandiseClassID
	AND B.MerchandiseUnitID=U.UnitID
	GROUP BY B.Barcode,C.MerchandiseClassName,B.MerchandiseName,U.UnitName
END

GO

